import tushare as ts
import pandas as pd
import os
import datetime

os.chdir('/Users/liujiannan/Desktop/')
pro = ts.pro_api('8b334e2d5d482292aa8b516c7f59dd6d23c0266dac3dc407dfb63ea1')
today = datetime.date.today()
lst = []
day = datetime.date(2021, 1, 1)
while day != (today+datetime.timedelta(days=1)):
    if day.isoweekday() > 5:
        day += datetime.timedelta(days=1)
        continue
    else:
        days = day.strftime('%Y%m%d')
        lst.append(pro.hsgt_top10(trade_date=days, market_type='1',fields='trade_date, ts_code, name, net_amount').
                   sort_values("net_amount", ascending=False))
        day += datetime.timedelta(days=1)
lst = lst[::-1]
result = pd.concat(lst)
today = today.strftime('%Y%m%d')

re_pivot = result.pivot_table(result,index=[u'name'])
re_pivot = re_pivot.sort_values("net_amount", ascending=False)

with pd.ExcelWriter(str(today)+"_北向_"+".xlsx") as writer:
    result.to_excel(writer, sheet_name="北向")
    re_pivot.to_excel(writer, sheet_name="汇总")


print("Finished")
